﻿using Kingdee.BOS.App.Data;
using Kingdee.BOS;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Util;
using Kingdee.BOS.Core;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Core.DynamicForm.PlugIn.Args;


namespace DM
{
    [HotUpdate]
    [Description("采购价格汇总表")]
    public class SummaryOfPurchasePrices : SysReportBaseService
    {
        /// 初始化事件：在此事件中，设置报表基本属性
        public override void Initialize()
        {
            base.Initialize();
            // 简单账表类型：普通、树形、分页
            this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
            this.ReportProperty.BillKeyFieldName = "FMATERIALID";
            // 报表名称
            this.ReportProperty.ReportName = new LocaleValue("采购价格汇总表", base.Context.UserLocale.LCID);
            this.IsCreateTempTableByPlugin = true;
            this.ReportProperty.IsUIDesignerColumns = true;
            this.ReportProperty.IsGroupSummary = true;
            this.ReportProperty.SimpleAllCols = false;
            // 单据主键：两行FID相同，则为同一单的两条分录，单据编号可以不重复显示
            this.ReportProperty.PrimaryKeyFieldName = "FMATERIALID";
            this.ReportProperty.IsDefaultOnlyDspSumAndDetailData = true;
            // 报表主键字段名：默认为FIDENTITYID，可以修改
            this.ReportProperty.IdentityFieldName = "FIDENTITYID";
            // 设置精度控制
            //List list = new List();
            //// 数量
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FQty",
            //    DecimalControlFieldName = "FUnitPrecision"
            //});
            //// 单价
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FTAXPRICE",
            //    DecimalControlFieldName = "FPRICEDIGITS"
            //});
            //// 金额
            //list.Add(new DecimalControlField
            //{
            //    ByDecimalControlFieldName = "FALLAMOUNT",
            //    DecimalControlFieldName = "FAMOUNTDIGITS"
            //});
            //this.ReportProperty.DecimalControlFieldList = list;
        }

        public override string GetTableName()
        {
            var result = base.GetTableName();
            return result;
        }

        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        {
            base.BuilderReportSqlAndTempTable(filter, tableName);
            // 拼接过滤条件 ： filter
            // 略
            // 默认排序字段：需要从filter中取用户设置的排序字段
            string seqFld = string.Format(base.KSQL_SEQ, "FMATERIALID");

            DateTime FDate;
            DateTime FEndDate;
            int YEAR = 0;
            string FMaterialid_idsum = "", FSuppiler_idsum = "", FPP_idsum = "";

            Kingdee.BOS.Orm.DataEntity.DynamicObject dyFilter = filter.FilterParameter.CustomFilter;
            StringBuilder SQLFilter = new StringBuilder();
            StringBuilder SQLFilter1 = new StringBuilder();
            FDate = Convert.ToDateTime(dyFilter["FDate"]);//期间
            //FEndDate = Convert.ToDateTime(dyFilter["FEndDate"]);//最后发货日期
            //过滤 日期
            if (!FDate.ToString().Contains("0001"))
            {
                //SQLFilter.Append("and t.FDATE>='" + FStartDate + "' and t.FDATE<='" + FEndDate + "'");
                YEAR = FDate.Year;
            }
            DynamicObjectCollection dyCollection = dyFilter["FMaterialid"] as DynamicObjectCollection;
            if (dyCollection.Count > 0)
            {
                foreach (var dobj in dyCollection)
                {
                    //string FMaterialid_id = Convert.ToString(dobj["FMATERIALID_ID"]);
                    DynamicObject d = dobj["FMaterialid"] as DynamicObject;
                    string FMaterialidNumber = d["Number"].ToString();
                    FMaterialid_idsum = FMaterialid_idsum + "'" + FMaterialidNumber + "'" + ",";

                }
                SQLFilter.Append("and tz.FNUMBER IN (" + FMaterialid_idsum.TrimEnd(',') + ")");
            }
            DynamicObjectCollection dyCollection1 = dyFilter["FSUPPLIERID"] as DynamicObjectCollection;
            if (dyCollection1.Count > 0)
            {
                foreach (var dobj in dyCollection1)
                {
                    //string FSuppiler_id = Convert.ToString(dobj["FSUPPLIERID_id"]);
                    DynamicObject d = dobj["FSUPPLIERID"] as DynamicObject;
                    string FSUPPLIERIDNumber = d["Number"].ToString();
                    FSuppiler_idsum = FSuppiler_idsum + "'" + FSUPPLIERIDNumber + "'" + ",";
                }
                SQLFilter.Append("and t25.FSUPPLIERNUMBER IN (" + FSuppiler_idsum.TrimEnd(',') + ")");
            }
            DynamicObjectCollection dyCollection2 = dyFilter["FPP"] as DynamicObjectCollection;
            if (dyCollection2.Count > 0)
            {
                foreach (var dobj in dyCollection2)
                {
                    string FPP_id = Convert.ToString(dobj["FPP_id"]);
                    FPP_idsum = FPP_idsum + "'" + FPP_id + "'" + ",";
                }
                SQLFilter.Append("and t6.FENTRYID IN (" + FPP_idsum.TrimEnd(',') + ")");
            }
            if (!Convert.ToString(dyFilter["FPL"]).Equals(""))
            {
                SQLFilter.Append("and tz.F_ORA_CATEGORY='" + dyFilter["FPL"] + "'");
            }
            if (!Convert.ToString(dyFilter["FGroup_id"]).Equals("0"))
            {
                SQLFilter.Append("and t2.FMATERIALGROUP='" + dyFilter["FGroup_id"] + "'");
            }
            //if (!Convert.ToString(dyFilter["FBillNo"]).Equals(""))
            //{
            //    SQLFilter1.Append("and t.FBillNo='" + dyFilter["FBillNo"] + "'");
            //}

            // 取数SQL
            string sql = string.Format(@"/*dialect*/
-------------------------1月份价格------------------------------
select * INTO #January from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T3.FNAME,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID,t3.fname ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
LEFT JOIN T_BD_SUPPLIER_L T3 ON T2.FSUPPLIERID=T3.FSUPPLIERID
where
dateadd(dd,-day('{4}-01-01')+1,'{4}-01-10')>=T.FEFFECTIVEDATE and dateadd(dd,-day('{4}-01-01'),dateadd(m,1,'{4}-01-11'))<T.FEXPIRYDATE
or ((T.FEFFECTIVEDATE >= dateadd(dd,-day('{4}-01-01')+1,'{4}-01-10') AND T.FEFFECTIVEDATE <= dateadd(dd,-day('{4}-01-01'),dateadd(m,1,'2024-01-11'))))
   OR (T.FEXPIRYDATE >= dateadd(dd,-day('{4}-01-01')+1,'{4}-01-10') AND T.FEXPIRYDATE <= dateadd(dd,-day('{4}-01-01'),dateadd(m,1,'{4}-01-11')))
   OR (T.FEFFECTIVEDATE <= dateadd(dd,-day('{4}-01-01')+1,'{4}-01-10') AND T.FEXPIRYDATE >= dateadd(dd,-day('{4}-01-01')+1,'{4}-01-10'))
   )t
   where t.row_num=1

-------------------------2月份价格------------------------------
select * INTO #February from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T3.FNAME,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID,t3.fname ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
LEFT JOIN T_BD_SUPPLIER_L T3 ON T2.FSUPPLIERID=T3.FSUPPLIERID
where
dateadd(dd,-day('{4}-02-01')+1,'{4}-02-10')>=T.FEFFECTIVEDATE and dateadd(dd,-day('{4}-02-01'),dateadd(m,1,'{4}-02-11'))<T.FEXPIRYDATE
or ((T.FEFFECTIVEDATE >= dateadd(dd,-day('{4}-02-01')+1,'{4}-02-10') AND T.FEFFECTIVEDATE <= dateadd(dd,-day('{4}-02-01'),dateadd(m,1,'2024-02-11'))))
   OR (T.FEXPIRYDATE >= dateadd(dd,-day('{4}-02-01')+1,'{4}-02-10') AND T.FEXPIRYDATE <= dateadd(dd,-day('{4}-02-01'),dateadd(m,1,'{4}-02-11')))
   OR (T.FEFFECTIVEDATE <= dateadd(dd,-day('{4}-02-01')+1,'{4}-02-10') AND T.FEXPIRYDATE >= dateadd(dd,-day('{4}-02-01')+1,'{4}-02-10'))
   )t
   where t.row_num=1

-------------------------3月份价格------------------------------
select * INTO #March from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T3.FNAME,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID,t3.fname ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
LEFT JOIN T_BD_SUPPLIER_L T3 ON T2.FSUPPLIERID=T3.FSUPPLIERID
where
dateadd(dd,-day('{4}-03-01')+1,'{4}-03-10')>=T.FEFFECTIVEDATE and dateadd(dd,-day('{4}-03-01'),dateadd(m,1,'{4}-03-11'))<T.FEXPIRYDATE
or ((T.FEFFECTIVEDATE >= dateadd(dd,-day('{4}-03-01')+1,'{4}-03-10') AND T.FEFFECTIVEDATE <= dateadd(dd,-day('{4}-03-01'),dateadd(m,1,'2024-03-11'))))
   OR (T.FEXPIRYDATE >= dateadd(dd,-day('{4}-03-01')+1,'{4}-03-10') AND T.FEXPIRYDATE <= dateadd(dd,-day('{4}-03-01'),dateadd(m,1,'{4}-03-11')))
   OR (T.FEFFECTIVEDATE <= dateadd(dd,-day('{4}-03-01')+1,'{4}-03-10') AND T.FEXPIRYDATE >= dateadd(dd,-day('{4}-03-01')+1,'{4}-03-10'))
   )t
   where t.row_num=1

-------------------------4月份价格------------------------------
select * INTO #April from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T3.FNAME,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID,t3.fname ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
LEFT JOIN T_BD_SUPPLIER_L T3 ON T2.FSUPPLIERID=T3.FSUPPLIERID
where
dateadd(dd,-day('{4}-04-01')+1,'{4}-04-10')>=T.FEFFECTIVEDATE and dateadd(dd,-day('{4}-04-01'),dateadd(m,1,'{4}-04-11'))<T.FEXPIRYDATE
or ((T.FEFFECTIVEDATE >= dateadd(dd,-day('{4}-04-01')+1,'{4}-04-10') AND T.FEFFECTIVEDATE <= dateadd(dd,-day('{4}-04-01'),dateadd(m,1,'2024-04-11'))))
   OR (T.FEXPIRYDATE >= dateadd(dd,-day('{4}-04-01')+1,'{4}-04-10') AND T.FEXPIRYDATE <= dateadd(dd,-day('{4}-04-01'),dateadd(m,1,'{4}-04-11')))
   OR (T.FEFFECTIVEDATE <= dateadd(dd,-day('{4}-04-01')+1,'{4}-04-10') AND T.FEXPIRYDATE >= dateadd(dd,-day('{4}-04-01')+1,'{4}-04-10'))
   )t
   where t.row_num=1

-------------------------5月份价格------------------------------
select * INTO #May from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T3.FNAME,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID,t3.fname ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
LEFT JOIN T_BD_SUPPLIER_L T3 ON T2.FSUPPLIERID=T3.FSUPPLIERID
where
dateadd(dd,-day('{4}-05-01')+1,'{4}-05-10')>=T.FEFFECTIVEDATE and dateadd(dd,-day('{4}-05-01'),dateadd(m,1,'{4}-05-11'))<T.FEXPIRYDATE
or ((T.FEFFECTIVEDATE >= dateadd(dd,-day('{4}-05-01')+1,'{4}-05-10') AND T.FEFFECTIVEDATE <= dateadd(dd,-day('{4}-05-01'),dateadd(m,1,'2024-05-11'))))
   OR (T.FEXPIRYDATE >= dateadd(dd,-day('{4}-05-01')+1,'{4}-05-10') AND T.FEXPIRYDATE <= dateadd(dd,-day('{4}-05-01'),dateadd(m,1,'{4}-05-11')))
   OR (T.FEFFECTIVEDATE <= dateadd(dd,-day('{4}-05-01')+1,'{4}-05-10') AND T.FEXPIRYDATE >= dateadd(dd,-day('{4}-05-01')+1,'{4}-05-10'))
   )t
   where t.row_num=1

-------------------------6月份价格------------------------------
select * INTO #June from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T3.FNAME,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID,t3.fname ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
LEFT JOIN T_BD_SUPPLIER_L T3 ON T2.FSUPPLIERID=T3.FSUPPLIERID
where
dateadd(dd,-day('{4}-06-01')+1,'{4}-06-10')>=T.FEFFECTIVEDATE and dateadd(dd,-day('{4}-06-01'),dateadd(m,1,'{4}-06-11'))<T.FEXPIRYDATE
or ((T.FEFFECTIVEDATE >= dateadd(dd,-day('{4}-06-01')+1,'{4}-06-10') AND T.FEFFECTIVEDATE <= dateadd(dd,-day('{4}-06-01'),dateadd(m,1,'2024-06-11'))))
   OR (T.FEXPIRYDATE >= dateadd(dd,-day('{4}-06-01')+1,'{4}-06-10') AND T.FEXPIRYDATE <= dateadd(dd,-day('{4}-06-01'),dateadd(m,1,'{4}-06-11')))
   OR (T.FEFFECTIVEDATE <= dateadd(dd,-day('{4}-06-01')+1,'{4}-06-10') AND T.FEXPIRYDATE >= dateadd(dd,-day('{4}-06-01')+1,'{4}-06-10'))
   )t
   where t.row_num=1

-------------------------7月份价格------------------------------
select * INTO #July from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T3.FNAME,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID,t3.fname ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
LEFT JOIN T_BD_SUPPLIER_L T3 ON T2.FSUPPLIERID=T3.FSUPPLIERID
where
dateadd(dd,-day('{4}-07-01')+1,'{4}-07-10')>=T.FEFFECTIVEDATE and dateadd(dd,-day('{4}-07-01'),dateadd(m,1,'{4}-07-11'))<T.FEXPIRYDATE
or ((T.FEFFECTIVEDATE >= dateadd(dd,-day('{4}-07-01')+1,'{4}-07-10') AND T.FEFFECTIVEDATE <= dateadd(dd,-day('{4}-07-01'),dateadd(m,1,'2024-07-11'))))
   OR (T.FEXPIRYDATE >= dateadd(dd,-day('{4}-07-01')+1,'{4}-07-10') AND T.FEXPIRYDATE <= dateadd(dd,-day('{4}-07-01'),dateadd(m,1,'{4}-07-11')))
   OR (T.FEFFECTIVEDATE <= dateadd(dd,-day('{4}-07-01')+1,'{4}-07-10') AND T.FEXPIRYDATE >= dateadd(dd,-day('{4}-07-01')+1,'{4}-07-10'))
   )t
   where t.row_num=1

-------------------------8月份价格------------------------------
select * INTO #August from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T3.FNAME,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID,t3.fname ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
LEFT JOIN T_BD_SUPPLIER_L T3 ON T2.FSUPPLIERID=T3.FSUPPLIERID
where
dateadd(dd,-day('{4}-08-01')+1,'{4}-08-10')>=T.FEFFECTIVEDATE and dateadd(dd,-day('{4}-08-01'),dateadd(m,1,'{4}-08-11'))<T.FEXPIRYDATE
or ((T.FEFFECTIVEDATE >= dateadd(dd,-day('{4}-08-01')+1,'{4}-08-10') AND T.FEFFECTIVEDATE <= dateadd(dd,-day('{4}-08-01'),dateadd(m,1,'2024-08-11'))))
   OR (T.FEXPIRYDATE >= dateadd(dd,-day('{4}-08-01')+1,'{4}-08-10') AND T.FEXPIRYDATE <= dateadd(dd,-day('{4}-08-01'),dateadd(m,1,'{4}-08-11')))
   OR (T.FEFFECTIVEDATE <= dateadd(dd,-day('{4}-08-01')+1,'{4}-08-10') AND T.FEXPIRYDATE >= dateadd(dd,-day('{4}-08-01')+1,'{4}-08-10'))
   )t
   where t.row_num=1

-------------------------9月份价格------------------------------
select * INTO #September from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T3.FNAME,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID,t3.fname ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
LEFT JOIN T_BD_SUPPLIER_L T3 ON T2.FSUPPLIERID=T3.FSUPPLIERID
where
dateadd(dd,-day('{4}-09-01')+1,'{4}-09-10')>=T.FEFFECTIVEDATE and dateadd(dd,-day('{4}-09-01'),dateadd(m,1,'{4}-09-11'))<T.FEXPIRYDATE
or ((T.FEFFECTIVEDATE >= dateadd(dd,-day('{4}-09-01')+1,'{4}-09-10') AND T.FEFFECTIVEDATE <= dateadd(dd,-day('{4}-09-01'),dateadd(m,1,'2024-09-11'))))
   OR (T.FEXPIRYDATE >= dateadd(dd,-day('{4}-09-01')+1,'{4}-09-10') AND T.FEXPIRYDATE <= dateadd(dd,-day('{4}-09-01'),dateadd(m,1,'{4}-09-11')))
   OR (T.FEFFECTIVEDATE <= dateadd(dd,-day('{4}-09-01')+1,'{4}-09-10') AND T.FEXPIRYDATE >= dateadd(dd,-day('{4}-09-01')+1,'{4}-09-10'))
   )t
   where t.row_num=1

-------------------------10月份价格------------------------------
select * INTO #October from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T3.FNAME,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID,t3.fname ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
LEFT JOIN T_BD_SUPPLIER_L T3 ON T2.FSUPPLIERID=T3.FSUPPLIERID
where
dateadd(dd,-day('{4}-10-01')+1,'{4}-10-10')>=T.FEFFECTIVEDATE and dateadd(dd,-day('{4}-10-01'),dateadd(m,1,'{4}-10-11'))<T.FEXPIRYDATE
or ((T.FEFFECTIVEDATE >= dateadd(dd,-day('{4}-10-01')+1,'{4}-10-10') AND T.FEFFECTIVEDATE <= dateadd(dd,-day('{4}-10-01'),dateadd(m,1,'2024-10-11'))))
   OR (T.FEXPIRYDATE >= dateadd(dd,-day('{4}-10-01')+1,'{4}-10-10') AND T.FEXPIRYDATE <= dateadd(dd,-day('{4}-10-01'),dateadd(m,1,'{4}-10-11')))
   OR (T.FEFFECTIVEDATE <= dateadd(dd,-day('{4}-10-01')+1,'{4}-10-10') AND T.FEXPIRYDATE >= dateadd(dd,-day('{4}-10-01')+1,'{4}-10-10'))
   )t
   where t.row_num=1

-------------------------11月份价格------------------------------
select * INTO #November from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T3.FNAME,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID,t3.fname ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
LEFT JOIN T_BD_SUPPLIER_L T3 ON T2.FSUPPLIERID=T3.FSUPPLIERID
where
dateadd(dd,-day('{4}-11-01')+1,'{4}-11-10')>=T.FEFFECTIVEDATE and dateadd(dd,-day('{4}-11-01'),dateadd(m,1,'{4}-11-11'))<T.FEXPIRYDATE
or ((T.FEFFECTIVEDATE >= dateadd(dd,-day('{4}-11-01')+1,'{4}-11-10') AND T.FEFFECTIVEDATE <= dateadd(dd,-day('{4}-11-01'),dateadd(m,1,'2024-11-11'))) and T.FEXPIRYDATE='2100-01-01')
   OR (T.FEXPIRYDATE >= dateadd(dd,-day('{4}-11-01')+1,'{4}-11-10') AND T.FEXPIRYDATE <= dateadd(dd,-day('{4}-11-01'),dateadd(m,1,'{4}-11-11')))
   OR (T.FEFFECTIVEDATE <= dateadd(dd,-day('{4}-11-01')+1,'{4}-11-10') AND T.FEXPIRYDATE >= dateadd(dd,-day('{4}-11-01')+1,'{4}-11-10'))
   )t
   where t.row_num=1

-------------------------12月份价格------------------------------
select * INTO #December from (
select t.FMATERIALID,
t.FTaxPrice,
t1.FNUMBER,T3.FNAME,T2.FPRICETYPE,
ROW_NUMBER() OVER (PARTITION BY t.FMATERIALID,t3.fname ORDER BY t2.FPRICETYPE DESC, t.FTaxPrice DESC) AS row_num
from t_PUR_PriceListEntry T
left join T_BD_MATERIAL t1 on t.FMATERIALID=t1.FMATERIALID
LEFT JOIN t_PUR_PriceList T2 ON T.FID=T2.FID
LEFT JOIN T_BD_SUPPLIER_L T3 ON T2.FSUPPLIERID=T3.FSUPPLIERID
where
dateadd(dd,-day('{4}-12-01')+1,'{4}-12-10')>=T.FEFFECTIVEDATE and dateadd(dd,-day('{4}-12-01'),dateadd(m,1,'{4}-12-11'))<T.FEXPIRYDATE
or ((T.FEFFECTIVEDATE >= dateadd(dd,-day('{4}-12-01')+1,'{4}-12-10') AND T.FEFFECTIVEDATE <= dateadd(dd,-day('{4}-12-01'),dateadd(m,1,'2024-12-11'))) and T.FEXPIRYDATE='2100-01-01')
   OR (T.FEXPIRYDATE >= dateadd(dd,-day('{4}-12-01')+1,'{4}-12-10') AND T.FEXPIRYDATE <= dateadd(dd,-day('{4}-12-01'),dateadd(m,1,'{4}-12-11')))
   OR (T.FEFFECTIVEDATE <= dateadd(dd,-day('{4}-12-01')+1,'{4}-12-10') AND T.FEXPIRYDATE >= dateadd(dd,-day('{4}-12-01')+1,'{4}-12-10'))
   )t
   where t.row_num=1

select tz.FMATERIALID,
CONVERT(VARCHAR(100), tz.FCREATEDATE,23)FEFFECTIVEDATE,
CASE WHEN Tz.F_ORA_GOODSTATUS='1' THEN '正常' 
WHEN Tz.F_ORA_GOODSTATUS='2' THEN '淘汰' end FState,
tz.F_ORA_CHANNEL Fchannel,
t25.FSUPPLIER,
Tz.FNUMBER FMATERIALNO,
t6.FDATAVALUE FPP,
T8.FNAME FGROUP,
t9.FNAME FMATERIALNAME,
T9.FSPECIFICATION,
t11.FNAME FUNIT,
t24.FBOXSTANDARDQTY FXG,
Tz.FIMAGEFILESERVER F_UNW_TBTN_PICTUREFIELD_TQN,
case when t12.FTAXPRICE is not null then t12.FTAXPRICE else 0 end  FJanuary,
case when t13.FTAXPRICE is not null then t13.FTAXPRICE else 0 end  February,
case when t14.FTAXPRICE is not null then t14.FTAXPRICE else 0 end  FMarch,
case when t15.FTAXPRICE is not null then t15.FTAXPRICE else 0 end  FApril,
case when t16.FTAXPRICE is not null then t16.FTAXPRICE else 0 end  FMay,
case when t17.FTAXPRICE is not null then t17.FTAXPRICE else 0 end  FJune,
case when t18.FTAXPRICE is not null then t18.FTAXPRICE else 0 end  FJuly,
case when t19.FTAXPRICE is not null then t19.FTAXPRICE else 0 end  FAugust,
case when t20.FTAXPRICE is not null then t20.FTAXPRICE else 0 end  FSeptember,
case when t21.FTAXPRICE is not null then t21.FTAXPRICE else 0 end  FOctober,
case when t22.FTAXPRICE is not null then t22.FTAXPRICE else 0 end  FNovember,
case when t23.FTAXPRICE is not null then t23.FTAXPRICE else 0 end  FDecember
into #table
from T_BD_MATERIAL tz
LEFT JOIN (
SELECT DISTINCT T2.FNUMBER,T3.FNAME FSUPPLIER,T4.FNUMBER FSUPPLIERNUMBER FROM  t_PUR_PriceListEntry T 
LEFT JOIN t_PUR_PriceList T1 ON T.FID=T1.FID
LEFT JOIN T_BD_MATERIAL T2 ON T.FMATERIALID=T2.FMATERIALID
LEFT JOIN T_BD_SUPPLIER_L T3 ON T1.FSUPPLIERID=T3.FSUPPLIERID
LEFT JOIN T_BD_SUPPLIER T4 ON T3.FSUPPLIERID=T4.FSUPPLIERID
)T25 ON TZ.FNUMBER=T25.FNUMBER
left join T_BAS_ASSISTANTDATAENTRY_L t6 on tz.F_ora_PP=t6.FENTRYID
LEFT JOIN T_BD_MATERIALGROUP T7 ON Tz.FMATERIALGROUP=T7.FID
LEFT JOIN T_BD_MATERIALGROUP_L T8 ON T7.FID=T8.FID
left join T_BD_MATERIAL_L t9 on tz.FMATERIALID=t9.FMATERIALID
left join t_BD_MaterialBase t10 on tz.FMATERIALID=t10.FMATERIALID
left join T_BD_UNIT_L t11 on t10.FBASEUNITID=t11.FUNITID
left join #January t12 on tz.FNUMBER=t12.FNUMBER AND T12.FNAME=t25.FSUPPLIER
left join #February t13 on tz.FNUMBER=t13.FNUMBER AND T13.FNAME=t25.FSUPPLIER
left join #March t14 on tz.FNUMBER=t14.FNUMBER AND T14.FNAME=t25.FSUPPLIER
left join #April t15 on tz.FNUMBER=t15.FNUMBER AND T15.FNAME=t25.FSUPPLIER
left join #May t16 on tz.FNUMBER=t16.FNUMBER AND T16.FNAME=t25.FSUPPLIER
left join #June t17 on tz.FNUMBER=t17.FNUMBER AND T17.FNAME=t25.FSUPPLIER
left join #July t18 on tz.FNUMBER=t18.FNUMBER AND T18.FNAME=t25.FSUPPLIER
left join #August t19 on tz.FNUMBER=t19.FNUMBER AND T19.FNAME=t25.FSUPPLIER
left join #September t20 on tz.FNUMBER=t20.FNUMBER AND T20.FNAME=t25.FSUPPLIER
left join #October t21 on tz.FNUMBER=t21.FNUMBER AND T21.FNAME=t25.FSUPPLIER
left join #November t22 on tz.FNUMBER=t22.FNUMBER AND T22.FNAME=t25.FSUPPLIER
left join #December t23 on tz.FNUMBER=t23.FNUMBER AND T23.FNAME=t25.FSUPPLIER
left join t_BD_MaterialStock t24 on tz.FMATERIALID=t24.FMATERIALID

where tz.FDOCUMENTSTATUS='C' {2} and tz.FCreateOrgId=tz.FUseOrgId
select distinct * INTO #TABLE2 from #table
SELECT distinct *,{0} INTO {1} FROM #TABLE2
                                        ", seqFld, tableName, SQLFilter, SQLFilter1, YEAR);
            DBUtils.ExecuteDynamicObject(this.Context, sql);
        }

        protected override string GetIdentityFieldIndexSQL(string tableName)
        {
            string result = base.GetIdentityFieldIndexSQL(tableName);
            return result;
        }

        protected override void ExecuteBatch(List<string> listSql)
        {
            base.ExecuteBatch(listSql);
        }

        protected override string GetSummaryColumsSQL(List<SummaryField> summaryFields)
        {
            var result = base.GetSummaryColumsSQL(summaryFields);
            return result;
        }

        protected override System.Data.DataTable GetListData(string sSQL)
        {
            var result = base.GetListData(sSQL);
            return result;
        }

        protected override System.Data.DataTable GetReportData(IRptParams filter)
        {
            var result = base.GetReportData(filter);
            return result;
        }

        protected override System.Data.DataTable GetReportData(string tablename, IRptParams filter)
        {
            var result = base.GetReportData(tablename, filter);
            return result;
        }

        public override int GetRowsCount(IRptParams filter)
        {
            var result = base.GetRowsCount(filter);
            return result;
        }

        protected override string BuilderFromWhereSQL(IRptParams filter)
        {
            string result = base.BuilderFromWhereSQL(filter);
            return result;
        }

        protected override string BuilderSelectFieldSQL(IRptParams filter)
        {
            string result = base.BuilderSelectFieldSQL(filter);
            return result;
        }

        protected override string BuilderTempTableOrderBySQL(IRptParams filter)
        {
            string result = base.BuilderTempTableOrderBySQL(filter);
            return result;
        }

        public override void CloseReport()
        {
            base.CloseReport();
        }

        protected override string CreateGroupSummaryData(IRptParams filter, string tablename)
        {
            string result = base.CreateGroupSummaryData(filter, tablename);
            return result;
        }

        protected override void CreateTempTable(string sSQL)
        {
            base.CreateTempTable(sSQL);
        }

        public override void DropTempTable()
        {
            base.DropTempTable();
        }

        public override System.Data.DataTable GetList(IRptParams filter)
        {
            var result = base.GetList(filter);
            return result;
        }

        public override List<long> GetOrgIdList(IRptParams filter)
        {
            var result = base.GetOrgIdList(filter);
            return result;
        }

        public override List<Kingdee.BOS.Core.Metadata.TreeNode> GetTreeNodes(IRptParams filter)
        {
            var result = base.GetTreeNodes(filter);
            return result;
        }
    }
}
